﻿using DataAccessService.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccessService.Repository
{
    public class MsSQLExcuteRepository: BaseRepository
    {
        /// <summary>
        /// 执行sql，返回执行结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExcuteSQL(SqlConnection conn, string sql)
        {
            try
            {                
                SqlCommand command = new SqlCommand(sql, conn);
                command.CommandTimeout = 60000;
                int result = command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ExcuteQuerySQL<T>(SqlConnection conn, string sql)
        {
            List<T> items = new List<T>();
            try
            {
                DataSet dataSet = new DataSet();

                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dataSet);

                items = ConvertToEntity<T>(dataSet);
                return items;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return items;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExcuteQuerySQL(SqlConnection conn, string sql)
        {
            DataSet dataSet = new DataSet();
            try
            {              

                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dataSet);                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dataSet;
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExcuteQuerySQL(SqlConnection conn,SqlTransaction tran, string sql)
        {
            DataSet dataSet = new DataSet();
            try
            {

                SqlCommand command = new SqlCommand(sql, conn, tran);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dataSet;
        }

        /// <summary>
        /// 执行sql，返回执行结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran">事务</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExcuteSQL(SqlConnection conn,SqlTransaction tran, string sql)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, conn, tran);
                int result = command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran">事务</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ExcuteQuerySQL<T>(SqlConnection conn, SqlTransaction tran, string sql)
        {
            List<T> items = new List<T>();
            try
            {
                DataSet dataSet = new DataSet();

                SqlCommand command = new SqlCommand(sql, conn, tran);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dataSet);

                items = ConvertToEntity<T>(dataSet);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return items;
        }

    }
}
